﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Configuration;
using ADA.Entity;
using System.Data;
using System.Data.SqlClient;

namespace ADA.Data
{
    public class DALNews
    {
        //private string conStr = ConfigurationManager.AppSettings["connectionString"];
        private string conStr = Common.CONNECTION_STRING;

        public DataTable getAllNews()
        {
            DataTable tblNewsInfo = new DataTable();

            string sql = @"SELECT   [News].[ID],
                                    [News].[Title],
                                    [Admin].[Name],
                                    [News].[PostDate]
                            FROM    [News], [Admin]
                            WHERE   [News].[AdminID]=[Admin].[ID]";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblNewsInfo);
            return tblNewsInfo;
        }

        public void updatePoster(short oldAdminID, short newAdminID)
        {
            string sql = @"UPDATE   [News]
                            SET     [News].[AdminID] = @newAdminID 
                            WHERE   [News].[AdminID] = @oldAdminID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@oldAdminID", SqlDbType.TinyInt).Value = oldAdminID;
            cmd.Parameters.Add("@newAdminID", SqlDbType.TinyInt).Value = newAdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public News getNewsInfo(int newsID)
        {
            News news = new News();
            DataTable tblNewsInfo = new DataTable();

            string sql = @"SELECT   [News].[Title],
                                    [News].[TitleJP],
                                    [News].[Image],
                                    [News].[Content],
                                    [News].[ContentJP]
                            FROM    [News]
                            WHERE [News].[ID] = @newsID";

            SqlConnection conn = new SqlConnection(conStr);
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;

            SqlDataAdapter da = new SqlDataAdapter(cmd);
            da.Fill(tblNewsInfo);

            if (tblNewsInfo.Rows.Count > 0)
            {
                news.ID = newsID;
                news.Title = Convert.ToString(tblNewsInfo.Rows[0]["Title"]).Trim();
                news.TitleJP = Convert.ToString(tblNewsInfo.Rows[0]["TitleJP"]).Trim();
                news.Image = Convert.ToString(tblNewsInfo.Rows[0]["Image"]).Trim();
                news.Content = Convert.ToString(tblNewsInfo.Rows[0]["Content"]).Trim();
                news.ContentJP = Convert.ToString(tblNewsInfo.Rows[0]["ContentJP"]).Trim();
            }

            return news;
        }

        public void insertNews(News news)
        {
            string sql = @"INSERT INTO [News]
                                       ([Title]
                                       ,[TitleJP]
                                       ,[Image]
                                       ,[Content]
                                       ,[ContentJP]
                                       ,[PostDate]
                                       ,[AdminID])
                                 VALUES
                                       (@title
                                       ,@titleJP
                                       ,@image
                                       ,@content
                                       ,@contentJP
                                       ,@postDate
                                       ,@adminID)";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = news.Title;
            cmd.Parameters.Add("@titleJP", SqlDbType.NVarChar).Value = news.TitleJP;
            cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = news.Image;
            cmd.Parameters.Add("@content", SqlDbType.NVarChar).Value = news.Content;
            cmd.Parameters.Add("@contentJP", SqlDbType.NVarChar).Value = news.ContentJP;
            cmd.Parameters.Add("@postDate", SqlDbType.Date).Value = news.PostDate;
            cmd.Parameters.Add("@adminID", SqlDbType.TinyInt).Value = news.AdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void updateNews(News news)
        {
            string sql = @"UPDATE   [News]
                            SET     [Title] = @title
                                    ,[TitleJP] = @titleJP
                                    ,[Image] = @image
                                    ,[Content] = @content
                                    ,[ContentJP] = @contentJP
                                    ,[PostDate] = @postDate
                                    ,[AdminID] = @adminID
                            WHERE   [News].[ID]=@newsID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = news.ID;
            cmd.Parameters.Add("@title", SqlDbType.NVarChar).Value = news.Title;
            cmd.Parameters.Add("@titleJP", SqlDbType.NVarChar).Value = news.TitleJP;
            cmd.Parameters.Add("@image", SqlDbType.VarChar).Value = news.Image;
            cmd.Parameters.Add("@content", SqlDbType.NVarChar).Value = news.Content;
            cmd.Parameters.Add("@contentJP", SqlDbType.NVarChar).Value = news.ContentJP;
            cmd.Parameters.Add("@postDate", SqlDbType.Date).Value = news.PostDate;
            cmd.Parameters.Add("@adminID", SqlDbType.TinyInt).Value = news.AdminID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }

        public void deleteNews(int newsID)
        {
            string sql = @"DELETE FROM [News]
                                WHERE [News].[ID]=@newsID";

            SqlConnection conn = new SqlConnection(conStr);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);

            cmd.Parameters.Add("@newsID", SqlDbType.Int).Value = newsID;

            cmd.ExecuteNonQuery();
            conn.Close();
        }
    }
}
